﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using LeadVinaglue.Models;
using LeadVinaglue.Models.ViewPages;
using System.Data.SqlClient;
using LeadVinaglue.Helpers;
using System.Data;

namespace LeadVinaglue.Repository
{
    public class ProductManagementRepository : BaseManagementRepository
    {
        LeadVinaglueEntities entities = new LeadVinaglueEntities();

        #region User Client
        public ProductManagementModel GetModelByUser(int userId, string pageType,
            string roleName,
            string keywords,
            string startDate,
            string endDate,
            string[] categoryId,
            string sortBy,
            string sortOrder,
            int pageSize,
            int pageIndex,
            int id)
        {
            // Update status
            if (id > 0)
            {
                var product = GetSingle(id);
                product.Active = !product.Active;
                Update(product);
            }

            // Set value
            ProductManagementModel model = new ProductManagementModel();
            int totalRecord = 0;
            model.ListProduct = GetAllByUser(userId, keywords, startDate, endDate, categoryId, sortBy, sortOrder, pageSize, pageIndex, ref totalRecord);

            PaginateModel paging = new PaginateModel();

            if (totalRecord > 0)
            {
                paging.PageIndex = pageIndex;
                paging.PageSize = pageSize;
                paging.TotalRecords = totalRecord;
                paging.TotalPages = (totalRecord % pageSize == 0) ? (totalRecord / pageSize) : (totalRecord / pageSize + 1);
            }
            else
            {
                paging.PageIndex = 1;
                paging.PageSize = 10;
                paging.TotalRecords = 0;
                paging.TotalPages = 1;
            }
            model.Paging = paging;

            if (id > 0)
            {
                model.ProductInfo = GetSingle(id);
            }

            return model;
        }

        public ProductManagementModel GetModelByUser(int userId, string pageType, string roleName, int pageSize, int pageIndex)
        {
            ProductManagementModel model = new ProductManagementModel();
            int totalRecord = 0;

            model.HeaderView = GetHeader();
            model.MenuItem = GetMenuItems(roleName, pageType);
            model.ListProduct = GetAllByUser(userId, string.Empty, pageSize, pageIndex, ref totalRecord);
            model.ListCategory = new CategoryRepository().GetAllItems();

            PaginateModel paging = new PaginateModel();

            if (totalRecord > 0)
            {
                paging.PageIndex = pageIndex;
                paging.PageSize = pageSize;
                paging.TotalRecords = totalRecord;
                paging.TotalPages = (totalRecord % pageSize == 0) ? (totalRecord / pageSize) : (totalRecord / pageSize + 1);
            }
            else
            {
                paging.PageIndex = 1;
                paging.PageSize = 10;
                paging.TotalRecords = 0;
                paging.TotalPages = 1;
            }
            model.Paging = paging;

            // Add Options
            model.ListRangeTime = GetRange();

            return model;
        }
        
        public ProductManagementModel GetModelByUser(int userId, string pageType, string roleName, string keywords, string fieldName, int pageSize, int pageIndex)
        {
            ProductManagementModel model = new ProductManagementModel();
            int totalRecord = 0;

            model.HeaderView = GetHeader();
            model.MenuItem = GetMenuItems(roleName, pageType);
            model.ListProduct = GetAllByUser(userId, keywords, fieldName, pageSize, pageIndex, ref totalRecord);

            PaginateModel paging = new PaginateModel();

            if (totalRecord > 0)
            {
                paging.PageIndex = pageIndex;
                paging.PageSize = pageSize;
                paging.TotalRecords = totalRecord;
                paging.TotalPages = (totalRecord % pageSize == 0) ? (totalRecord / pageSize) : (totalRecord / pageSize + 1);
            }
            else
            {
                paging.PageIndex = 1;
                paging.PageSize = 10;
                paging.TotalRecords = 0;
                paging.TotalPages = 1;
            }
            model.Paging = paging;

            // Add Options
            model.ListRangeTime = GetRange();

            return model;
        }
        #endregion

        public ProductManagementModel GetModel(string pageType, 
            string roleName, 
            string keywords, 
            string startDate,
            string endDate,
            string[] categoryId,
            string sortBy,
            string sortOrder,
            int pageSize,
            int pageIndex,
            int id)
        {
            // Update status
            if (id > 0)
            {
                Delete(id);
            }

            // Set value
            ProductManagementModel model = new ProductManagementModel();
            int totalRecord = 0;
            model.ListProduct = GetAll(keywords, startDate, endDate, categoryId, sortBy, sortOrder, pageSize, pageIndex, ref totalRecord);

            PaginateModel paging = new PaginateModel();

            if (totalRecord > 0)
            {
                paging.PageIndex = pageIndex;
                paging.PageSize = pageSize;
                paging.TotalRecords = totalRecord;
                paging.TotalPages = (totalRecord % pageSize == 0) ? (totalRecord / pageSize) : (totalRecord / pageSize + 1);
            }
            else
            {
                paging.PageIndex = 1;
                paging.PageSize = 10;
                paging.TotalRecords = 0;
                paging.TotalPages = 1;
            }
            model.Paging = paging;

            if (id > 0)
            {
                model.ProductInfo = GetSingle(id);
            }

            return model;
        }

        public ProductManagementModel GetModel(string pageType, string roleName, int pageSize, int pageIndex)
        {
            ProductManagementModel model = new ProductManagementModel();
            int totalRecord = 0;

            model.HeaderView = GetHeader();
            model.MenuItem = GetMenuItems(roleName, pageType);
            model.ListProduct = GetAll(string.Empty, pageSize, pageIndex, ref totalRecord);

            PaginateModel paging = new PaginateModel();

            if (totalRecord > 0)
            {
                paging.PageIndex = pageIndex;
                paging.PageSize = pageSize;
                paging.TotalRecords = totalRecord;
                paging.TotalPages = (totalRecord % pageSize == 0) ? (totalRecord / pageSize) : (totalRecord / pageSize + 1);
            }
            else
            {
                paging.PageIndex = 1;
                paging.PageSize = 10;
                paging.TotalRecords = 0;
                paging.TotalPages = 1;
            }
            model.Paging = paging;

            // Add Options
            model.ListRangeTime = GetRange();
            model.ListCategory = new CategoryRepository().GetAllItems();

            return model;
        }

        public ProductManagementModel GetModel(string pageType, string roleName, string keywords, string fieldName, int pageSize, int pageIndex)
        {
            ProductManagementModel model = new ProductManagementModel();
            int totalRecord = 0;

            model.HeaderView = GetHeader();
            model.MenuItem = GetMenuItems(roleName, pageType);
            model.ListProduct = GetAll(keywords, fieldName, pageSize, pageIndex, ref totalRecord);

            PaginateModel paging = new PaginateModel();

            if (totalRecord > 0)
            {
                paging.PageIndex = pageIndex;
                paging.PageSize = pageSize;
                paging.TotalRecords = totalRecord;
                paging.TotalPages = (totalRecord % pageSize == 0) ? (totalRecord / pageSize) : (totalRecord / pageSize + 1);
            }
            else
            {
                paging.PageIndex = 1;
                paging.PageSize = 10;
                paging.TotalRecords = 0;
                paging.TotalPages = 1;
            }
            model.Paging = paging;

            // Add Options
            model.ListRangeTime = GetRange();

            return model;
        }
        
        public ProductManagementModel GetModelSearch(string keywords, string fieldName, int pageSize, int pageIndex)
        {
            ProductManagementModel model = new ProductManagementModel();
            int totalRecord = 0;

            model.ListProduct = GetAll(keywords, fieldName, pageSize, pageIndex, ref totalRecord);

            PaginateModel paging = new PaginateModel();

            if (totalRecord > 0)
            {
                paging.PageIndex = pageIndex;
                paging.PageSize = pageSize;
                paging.TotalRecords = totalRecord;
                paging.TotalPages = (totalRecord % pageSize == 0) ? (totalRecord / pageSize) : (totalRecord / pageSize + 1);
            }
            else
            {
                paging.PageIndex = 1;
                paging.PageSize = 10;
                paging.TotalRecords = 0;
                paging.TotalPages = 1;
            }
            model.Paging = paging;

            // Add Options
            model.ListRangeTime = GetRange();

            return model;
        }

        public ProductManagementModel GetModel(string pageType, string roleName, int id)
        {
            ProductManagementModel model = new ProductManagementModel();

            model.HeaderView = GetHeader();
            model.MenuItem = GetMenuItems(roleName, pageType);
            model.ListCategory = new CategoryRepository().GetAllItems();
            model.ListSubCategory = new SubCategoryRepository().GetAllItems();
            model.PaymentList = new PaymentManagementRepository().GetAll();
            model.ListLanguage = new LanguageManagementRepository().GetAll();
            model.ListDocumentType = new DocumentTypeManagementRepository().GetAll();

            if (pageType == Helpers.Constant.PRODUCT)
            {
                model.ListCategory.RemoveAt(0);
                model.ListSubCategory.RemoveAt(0);
            }

            model.ProductInfo = GetSingle(id);

            return model;
        }

        public ProductPreviewModel GetModelPreview(int id)
        {
            ProductPreviewModel model = new ProductPreviewModel();

            // Bild info
            var product = GetSingle(id);
            if (product != null)
            {
                // Product Info
                model.ID = product.ID;
                model.Title = product.Title;
                model.LongDesc = product.LongDesc;
                model.DateCreated = product.DateCreated;

                // Get User
                if (Convert.ToInt32(product.CreatedByUserID) > 0)
                {
                    model.Username = new UserRepository().GetSingle(Convert.ToInt32(product.CreatedByUserID)).Fullname;
                }

                if (Convert.ToInt32(product.CategoryID) > 0)
                {
                    model.CategoryName = new CategoryRepository().GetSingle(product.CategoryID.Value).Name;
                }
                if (Convert.ToInt32(product.SubCategoryID) > 0)
                {
                    model.SubCategoryName = new SubCategoryRepository().GetSingle(product.SubCategoryID.Value).Name;
                }
            }

            return model;
        }
        
        #region #Private functions
        private List<OptionModel> GetRange()
        {
            List<OptionModel> list = new List<OptionModel>();

            OptionModel model;
            model = new OptionModel { ID = DateTime.Now.ToString("MM/dd/yyyy") + "|" + DateTime.Now.ToString("MM/dd/yyyy"), Name = "Hôm nay" };
            list.Add(model);

            model = new OptionModel { ID = DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") + "|" + DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy"), Name = "Hôm qua" };
            list.Add(model);

            model = new OptionModel { ID = DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy") + "|" + DateTime.Now.ToString("MM/dd/yyyy"), Name = "7 ngày trước" };
            list.Add(model);

            model = new OptionModel { ID = DateTime.Now.AddDays(-30).ToString("MM/dd/yyyy") + "|" + DateTime.Now.ToString("MM/dd/yyyy"), Name = "30 ngày trước" };
            list.Add(model);

            DateTime startDate = new DateTime();
            DateTime endDate = new DateTime();

            // before this month
            startDate = Utility.LeadTools.GetFirstDayOfMonth(DateTime.Now.AddMonths(-1).Month, DateTime.Now.Year);
            endDate = Utility.LeadTools.GetLastDayOfMonth(startDate);
            model = new OptionModel { ID = startDate.ToString("MM/dd/yyyy") + "|" + endDate.ToString("MM/dd/yyyy"), Name = "Tháng trước" };
            list.Add(model);

            // before 2 month
            startDate = Utility.LeadTools.GetFirstDayOfMonth(DateTime.Now.AddMonths(-2).Month, DateTime.Now.Year);
            endDate = Utility.LeadTools.GetLastDayOfMonth(startDate.AddMonths(1));
            model = new OptionModel { ID = startDate.ToString("MM/dd/yyyy") + "|" + endDate.ToString("MM/dd/yyyy"), Name = "2 tháng trước" };
            list.Add(model);

            // Before 3 month
            startDate = Utility.LeadTools.GetFirstDayOfMonth(DateTime.Now.AddMonths(-3).Month, DateTime.Now.Year);
            endDate = Utility.LeadTools.GetLastDayOfMonth(startDate.AddMonths(2));
            model = new OptionModel { ID = startDate.ToString("MM/dd/yyyy") + "|" + endDate.ToString("MM/dd/yyyy"), Name = "3 tháng trước" };
            list.Add(model);

            // This month
            startDate = Utility.LeadTools.GetFirstDayOfMonth(DateTime.Now.Month, DateTime.Now.Year);
            endDate = Utility.LeadTools.GetLastDayOfMonth(startDate);
            model = new OptionModel { ID = startDate.ToString("MM/dd/yyyy") + "|" + endDate.ToString("MM/dd/yyyy"), Name = "Tháng này" };
            list.Add(model);

            // All time
            model = new OptionModel { ID = "-1", Name = "Tất cả" };
            list.Add(model);

            return list;
        }
        
        private List<ProductModel> GetAll(string keywords, string startDate, string endDate, string[] categoryId, string sortBy, string sortOrder, int pageSize, int pageIndex, ref int total)
        {
            List<ProductModel> items = new List<ProductModel>();
            List<int> catList = new List<int>();
            List<int> arrStatus = new List<int>();

            var products = (from c in entities.Products
                           select new ProductModel
                           {
                               ID = c.ID,
                               GUID = c.GUID,
                               Title = c.Title,
                               ImageURL = c.ImageURL,
                               Reviewed = c.Reviewed,
                               DateCreated = c.DateCreated,
                               Active = c.Active
                           });

            // Category list
            if (categoryId.Length > 0 && int.Parse(categoryId[0]) > 0)
            {
                for (int i = 0; i < categoryId.Length; i++)
                {
                    catList.Add(int.Parse(categoryId[i]));
                }
                List<int> listProduct = GetAllProductIDByArrayCategory(catList);
                products = products.Where(c => listProduct.Contains(c.ID));
            }
                        
            // Check conditions
            if (!string.IsNullOrEmpty(keywords))
                products = products.Where(c => c.Title.Contains(keywords));

            DateTime isStartDate;
            if (!string.IsNullOrEmpty(startDate) && DateTime.TryParse(startDate, out isStartDate))
            {
                DateTime sDate = Convert.ToDateTime(startDate);
                products = products.Where(c => c.DateCreated >= sDate);
            }
            DateTime isEndDate;
            if (!string.IsNullOrEmpty(endDate) && DateTime.TryParse(endDate, out isEndDate))
            {
                DateTime eDate = Convert.ToDateTime(endDate);
                products = products.Where(c => c.DateCreated <= eDate);
            }
            
            // Get total count
            try
            {
                if (products != null && products.Count() > 0)
                {
                    total = products.Count();

                    // Sort by Column|Order
                    sortOrder = sortOrder.ToUpper();
                    sortBy = sortBy.ToUpper();
                    switch (sortBy)
                    {
                        case "TITLE":
                            {
                                if(sortOrder == "ASC")
                                    items = products.OrderBy(c => c.Title).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                else
                                    items = products.OrderByDescending(c => c.Title).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                break;
                            }

                        case "REVIEWED":
                            {
                                if (sortOrder == "ASC")
                                    items = products.OrderBy(c => c.Reviewed).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                else
                                    items = products.OrderByDescending(c => c.Reviewed).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                break;
                            }
                        case "ACTIVE":
                            {
                                if (sortOrder == "ASC")
                                    items = products.OrderBy(c => c.Active).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                else
                                    items = products.OrderByDescending(c => c.Active).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                break;
                            }
                        default:
                            {
                                if(sortOrder == "ASC")
                                    items = products.OrderByDescending(c => c.DateCreated).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                else
                                    items = products.OrderByDescending(c => c.DateCreated).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                break;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                Helpers.ErrorHandle.AddError(ex.Message);
            }

            // Add list
            for (int i = 0; i < items.Count; i++)
            {
                items[i].CreationDate = Convert.ToDateTime(items[i].DateCreated).ToString("dd-MM-yyyy hh:mm tt");
            }

            return items;
        }
        
        private List<ProductModel> GetAllByUser(int userId, string keywords, string startDate, string endDate, string[] categoryId, string sortBy, string sortOrder, int pageSize, int pageIndex, ref int total)
        {
            List<ProductModel> items = new List<ProductModel>();
            List<int> catList = new List<int>();
            List<int> arrStatus = new List<int>();
            var products = (from c in entities.Products
                            where c.CreatedByUserID == userId
                            select new ProductModel
                            {
                                ID = c.ID,
                                GUID = c.GUID,
                                Title = c.Title,
                                FileName = c.FileName,
                                FileType = c.FileType,
                                ImageURL = c.ImageURL,
                                Reviewed = c.Reviewed,
                                Downloaded = c.Downloaded,
                                DateCreated = c.DateCreated,
                                Active = c.Active
                            });

            // Category list
            if (categoryId.Length > 0 && int.Parse(categoryId[0]) > 0)
            {
                for (int i = 0; i < categoryId.Length; i++)
                {
                    catList.Add(int.Parse(categoryId[i]));
                }
                List<int> listProduct = GetAllProductIDByArrayCategory(catList);
                products = products.Where(c => listProduct.Contains(c.ID));
            }
            
            // Check conditions
            if (!string.IsNullOrEmpty(keywords))
                products = products.Where(c => c.Title.Contains(keywords));

            DateTime isStartDate;
            if (!string.IsNullOrEmpty(startDate) && DateTime.TryParse(startDate, out isStartDate))
            {
                DateTime sDate = Convert.ToDateTime(startDate);
                products = products.Where(c => c.DateCreated >= sDate);
            }
            DateTime isEndDate;
            if (!string.IsNullOrEmpty(endDate) && DateTime.TryParse(endDate, out isEndDate))
            {
                DateTime eDate = Convert.ToDateTime(endDate);
                products = products.Where(c => c.DateCreated <= eDate);
            }
            
            // Get total count
            try
            {
                if (products != null && products.Count() > 0)
                {
                    total = products.Count();

                    // Sort by Column|Order
                    sortOrder = sortOrder.ToUpper();
                    sortBy = sortBy.ToUpper();
                    switch (sortBy)
                    {
                        case "TITLE":
                            {
                                if(sortOrder == "ASC")
                                    items = products.OrderBy(c => c.Title).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                else
                                    items = products.OrderByDescending(c => c.Title).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                break;
                            }
                        case "REVIEWED":
                            {
                                if (sortOrder == "ASC")
                                    items = products.OrderBy(c => c.Reviewed).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                else
                                    items = products.OrderByDescending(c => c.Reviewed).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                break;
                            }
                        case "ACTIVE":
                            {
                                if (sortOrder == "ASC")
                                    items = products.OrderBy(c => c.Active).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                else
                                    items = products.OrderByDescending(c => c.Active).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                break;
                            }
                        default:
                            {
                                if(sortOrder == "ASC")
                                    items = products.OrderByDescending(c => c.DateCreated).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                else
                                    items = products.OrderByDescending(c => c.DateCreated).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                                break;
                            }
                    }
                }
            }
            catch (Exception ex)
            {
                Helpers.ErrorHandle.AddError(ex.Message);
            }

            // Add list
            for (int i = 0; i < items.Count; i++)
            {
                items[i].FileType = items[i].FileType.Replace(".", string.Empty);
                items[i].CreationDate = Convert.ToDateTime(items[i].DateCreated).ToString("dd-MM-yyyy hh:mm tt");
            }

            return items;
        }

        private List<ProductModel> GetAllByUser(int userId, string keywords, string fieldName, int pageSize, int pageIndex, ref int total)
        {
            List<ProductModel> list = new List<ProductModel>();
            string sqlEx = string.Empty;

            if (fieldName != "ID")
                sqlEx = string.Format("SELECT [ID], [TITLE], [REVIEWED], [DATECREATED], [ACTIVE], [Downloaded], [IsPublic], [FileName], [FileType] FROM [Product] WHERE [CreatedByUserId] = {0} AND [{1}] LIKE N'%{2}%'", userId, fieldName, keywords);
            else
                sqlEx = string.Format("SELECT [ID], [TITLE], [REVIEWED], [DATECREATED], [ACTIVE], [Downloaded], [IsPublic], [FileName], [FileType] FROM [Product] WHERE [CreatedByUserId] = {0} AND [{1}]={2}", userId, fieldName, keywords);

            try
            {
                // Execute a query
                using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlEx))
                {
                    while (item.Read())
                    {
                        ProductModel model = new ProductModel();

                        model.ID = item.GetInt32(0);
                        if (!item.IsDBNull(1))
                            model.Title = item.GetString(1);
                        if (!item.IsDBNull(2))
                            model.Reviewed = item.GetInt32(2);
                        if (!item.IsDBNull(3))
                            model.CreationDate = Convert.ToDateTime(item.GetDateTime(3)).ToString("dd-MM-yyyy hh:mm tt");
                        if (!item.IsDBNull(4))
                            model.Active = item.GetBoolean(4);
                        if (!item.IsDBNull(5))
                            model.Downloaded = item.GetInt32(5);
                        if (!item.IsDBNull(6))
                            model.IsPublic = item.GetBoolean(6);
                        if (!item.IsDBNull(7))
                            model.FileName = item.GetString(7);
                        if (!item.IsDBNull(8))
                            model.FileType = item.GetString(8);

                        model.ProductState = (model.Active)? "Hiển thị": "Chưa hiển thị";

                        list.Add(model);
                    }
                }

                total = list.Count;

                return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        private List<ProductModel> GetAll(string keywords, string fieldName, int pageSize, int pageIndex, ref int total)
        {
            List<ProductModel> list = new List<ProductModel>();
            string sqlEx = string.Empty;

            if (fieldName != "ID")
                sqlEx = string.Format("SELECT [ID], [TITLE], [REVIEWED], [DATECREATED], [ACTIVE], [Downloaded], [IsPublic], [FileName], [FileType] FROM [Product] WHERE [{0}] LIKE N'%{1}%' ORDER BY [ID] DESC", fieldName, keywords);
            else
                sqlEx = string.Format("SELECT [ID], [TITLE], [REVIEWED], [DATECREATED], [ACTIVE], [Downloaded], [IsPublic], [FileName], [FileType] FROM [Product] WHERE [{0}]={1} ORDER BY [ID] DESC", fieldName, keywords);

            try
            {
                // Execute a query
                using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlEx))
                {
                    while (item.Read())
                    {
                        ProductModel model = new ProductModel();

                        model.ID = item.GetInt32(0);
                        if (!item.IsDBNull(1))
                            model.Title = item.GetString(1);
                        if (!item.IsDBNull(2))
                            model.Reviewed = item.GetInt32(2);
                        if (!item.IsDBNull(3))
                            model.CreationDate = Convert.ToDateTime(item.GetDateTime(3)).ToString("dd-MM-yyyy hh:mm tt");
                        if (!item.IsDBNull(4))
                            model.Active = item.GetBoolean(4);

                        list.Add(model);
                    }
                }

                total = list.Count;

                return list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            catch
            {
                return null;
            }
        }

        private List<ProductModel> GetAllByUser(int userId, string keywords, int pageSize, int pageIndex, ref int total)
        {
            var banners = (from c in entities.Products
                           where c.CreatedByUserID == userId
                           select new ProductModel
                           {
                               ID = c.ID,
                               GUID = c.GUID,
                               Title = c.Title,
                               FileName = c.FileName,
                               FileType = c.FileType,
                               ImageURL = c.ImageURL,
                               Reviewed = c.Reviewed,
                               Downloaded = c.Downloaded,
                               DateCreated = c.DateCreated,
                               IsPublic = c.IsPublic,
                               Active = c.Active
                           });

            // Check conditions
            if (!string.IsNullOrEmpty(keywords))
                banners = banners.Where(c => c.Title.Contains(keywords));

            // Get total count
            total = banners.Count();
            var items = banners.OrderByDescending(c => c.ID).AsEnumerable().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();

            for (int i = 0; i < items.Count; i++)
            {
                items[i].FileType = items[i].FileType.Replace(".", string.Empty);
                items[i].CreationDate = Convert.ToDateTime(items[i].DateCreated).ToString("dd-MM-yyyy hh:mm tt");
            }

            return items;
        }

        private List<ProductModel> GetAll(string keywords, int pageSize, int pageIndex, ref int total)
        {
            var banners = (from c in entities.Products
                           select new ProductModel
                           {
                               ID = c.ID,
                               GUID = c.GUID,
                               Title = c.Title,
                               ImageURL = c.ImageURL,
                               Reviewed = c.Reviewed,
                               DateCreated = c.DateCreated,
                               Active = c.Active
                           });

            // Check conditions
            if (!string.IsNullOrEmpty(keywords))
                banners = banners.Where(c => c.Title.Contains(keywords));
            
            // Get total count
            total = banners.Count();

            var items = banners.OrderByDescending(c => c.DateCreated).ToList().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();

            for (int i = 0; i < items.Count; i++)
            {
                items[i].CreationDate = Convert.ToDateTime(items[i].DateCreated).ToString("dd-MM-yyyy hh:mm tt");
            }

            return items;
        }

        private List<int> GetAllProductIDByArrayCategory(List<int> catList)
        {
            List<int> list = new List<int>();

            var products = (from p in entities.Products
                            select new
                            {
                                ProductID = p.ID,
                                CategoryID = p.CategoryID
                            });

            if (catList != null && catList.Count > 0)
            {
                products = products.Where(c => catList.Contains(c.CategoryID.Value));
                
                // Bind to list
                foreach (var item in products)
                {
                    list.Add(item.ProductID);
                }
            }

            return list;
        }
        
        #endregion

        #region Reference function

        public Product GetSingle(int id)
        {
            return (from _object in entities.Products where _object.ID == id select _object).FirstOrDefault();
        }
        
        public void Create([Bind(Exclude = "ID, DateCreated")] Product _object)
        {
            _object.DateCreated = System.DateTime.Now;
            entities.AddToProducts(_object);
            entities.SaveChanges();
        }

        public void Delete(int id)
        {
            var _object = (from _list in entities.Products where _list.ID == id select _list).First();
            entities.DeleteObject(_object);
            entities.SaveChanges();
        }

        public void UpdateReview(Product _object)
        {
            //_object.DateCreated = System.DateTime.Now;
            entities.SaveChanges();
        }

        public void UpdateReview(long id, int reviewed)
        {
            string sqlEx = string.Format("UPDATE [Product] SET [Reviewed]={0} WHERE [ID]={1}", reviewed, id);
            try
            {
                // Execute a query
                int returnInt = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlEx);
            }
            catch
            {
            }
        }

        public void Update(Product _object)
        {
            //_object.DateCreated = System.DateTime.Now;
            entities.SaveChanges();
        }

        public void Approved(int id, int active)
        {
            string sqlEx = string.Format("UPDATE [Product] SET [Active]={0} WHERE [ID]={1}", active, id);
            try
            {
                // Execute a query
                int returnInt = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlEx);
            }
            catch
            {
            }
        }

        public void Save([Bind(Exclude = "ID")] Product _object)
        {
            _object.EntityKey = (from Product ac in entities.Products where ac.ID == _object.ID select ac).FirstOrDefault().EntityKey;
            _object.DateCreated = System.DateTime.Now;
            entities.SaveChanges();
        }

        #endregion
    }
}
